import os

import pandas as pd
from datetime import datetime, timedelta
from decimal import getcontext
from spreadsheet.data import ready


getcontext().prec = 7


def kucun_chengben_14_14_count_main(extract_dir):
    # 获取上月的年月
    today = datetime.today()
    first = today.replace(day=1)
    last_month = first - timedelta(days=1)
    last_month_str = last_month.strftime("%Y-%m")
    print(last_month_str)

    
    # 列出当前目录下的所有文件
    files_in_directory = os.listdir(extract_dir)

    if len(files_in_directory) == 1 and os.path.isdir(os.path.join(extract_dir, files_in_directory[0])):
        extract_dir = os.path.join(extract_dir, files_in_directory[0])
        files_in_directory = os.listdir(extract_dir)

    # 过滤出所有的 .xlsx 文件
    xlsx_file_name_list = [file for file in files_in_directory if not file.startswith(".~") and file.endswith('.xlsx')]


    for xlsx_file_name in xlsx_file_name_list:
        if "库存出入库流水" in xlsx_file_name:
            a_file_name = xlsx_file_name
            a_file_path = os.path.join(extract_dir, a_file_name)
        if "进销存台账" in xlsx_file_name:
            b_file_name = xlsx_file_name
            b_file_path = os.path.join(extract_dir, b_file_name)

    # 库存对应关系
    inv_sheet = ready("OcdUsaHHzhi3pDtJyfMcmQ9inZe", "Sheet1")
    inv_map = pd.DataFrame(inv_sheet[1:], columns=inv_sheet[0])

    # 读取文件
    a_file = pd.ExcelFile(a_file_path)
    b_file = pd.ExcelFile(b_file_path)

    # 处理A文件
    a_file_a = a_file.parse('file')

    # 筛选上月数据
    a_file_c = a_file_a[a_file_a['出入库时间'].str.contains(last_month_str)]

    # 新增"ERP仓库"列
    a_file_c = a_file_c.merge(inv_map[['仓库名称', 'ERP仓库']], on='仓库名称', how='left')

    # 判断是否存在 'Unnamed: 12' 和 'Unnamed: 14'，并重命名列
    rename_mapping = {}
    if 'Unnamed: 12' in a_file_c.columns:
        rename_mapping['业务类型'] = '业务类型编码'
        rename_mapping['Unnamed: 12'] = '业务类型'

    if 'Unnamed: 14' in a_file_c.columns:
        rename_mapping['库存类型'] = '库存类型编码'
        rename_mapping['Unnamed: 14'] = '库存类型'

    if '业务类型.1' in a_file_c.columns:
        rename_mapping['业务类型'] = '业务类型编码'
        rename_mapping['业务类型.1'] = '业务类型'

    if '库存类型.1' in a_file_c.columns:
        rename_mapping['库存类型'] = '库存类型编码'
        rename_mapping['库存类型.1'] = '库存类型'

    # 执行重命名
    a_file_c.rename(columns=rename_mapping, inplace=True)
    a_file_a.rename(columns=rename_mapping, inplace=True)

    a_file_a['货品编码'] = a_file_a['货品编码'].str.replace(r'-true$', '', regex=True)
    with pd.ExcelWriter(a_file_path,engine='openpyxl',mode='a',if_sheet_exists='replace') as writer:
        a_file_a.to_excel(writer, sheet_name='file', index=False)

    # 处理B文件
    b_file_a = b_file.parse('file')

    b_file_a['货品编码'] = b_file_a['货品编码'].str.replace(r'-true$', '', regex=True)
    with pd.ExcelWriter(b_file_path, engine='openpyxl', mode='a',if_sheet_exists='replace') as writer:
        b_file_a.to_excel(writer, sheet_name='file', index=False)

    # 筛选上月数据
    b_file_c = b_file_a[b_file_a['日期范围'].str.contains(last_month_str)]

    # 新增"ERP仓库"列
    b_file_c = b_file_c.merge(inv_map[['仓库名称', 'ERP仓库']], on='仓库名称', how='left')

    # 新增"接收"、"调拨"、"退货"三列
    # 计算"退货"列
    b_file_c['接收'] = 0
    b_file_c['调拨'] = 0

    toc_sales_return = a_file_c[a_file_c['业务类型'] == 'TOC销售退货']
    toc_sales_return.loc[:, '出入库时间'] = pd.to_datetime(toc_sales_return['出入库时间']).dt.date
    
    toc_sales_return_grouped = toc_sales_return.groupby(['货品编码', '仓库名称', '出入库时间'])['库存变动'].sum().reset_index()

    b_file_c.loc[:, '日期范围'] = pd.to_datetime(b_file_c['日期范围']).dt.date
    b_file_c = b_file_c.merge(toc_sales_return_grouped, left_on=['货品编码', '仓库名称', '日期范围'], right_on=['货品编码', '仓库名称', '出入库时间'], how='left')
    b_file_c['退货'] = b_file_c['库存变动'].fillna(0)

    b_file_c['接收'] = b_file_c['入库总量'] - b_file_c['退货'] - b_file_c['良品盘盈']

    # 删除不需要的列
    b_file_c = b_file_c.drop(columns=['出入库时间', '库存变动'])

    # 将 a_file_c 和 b_file_c 存入各自的子表 "2024-07"
    with pd.ExcelWriter(a_file_path, engine='openpyxl', mode='a',if_sheet_exists='replace') as writer:
        a_file_c.to_excel(writer, sheet_name=last_month_str, index=False)

    with pd.ExcelWriter(b_file_path, engine='openpyxl', mode='a',if_sheet_exists='replace') as writer:
        b_file_c.to_excel(writer, sheet_name=last_month_str, index=False)


    # 输出模板1文件
    template1_rows = []
    toc_sales_return = a_file_c[a_file_c['业务类型'] == 'TOC销售退货']
    for index, row in toc_sales_return.iterrows():
        print(row)
        template1_rows.append({
            '凭证类型（必填）': '入库',
            '业务单据类型（条件必填）': '销售退货入库-仅退货',
            '账务日期（必填）': last_month.strftime("%Y-%m-%d"),
            '凭证子库（条件必填）': row['ERP仓库'],
            '产品编码（条件必填）': row['货品编码'],
            '数量（条件必填）': abs(row['库存变动']),
            '存货状态（必填）': 'BAD' if '在库残次' == row['库存类型'] else "NEW",
            '备注': row['业务主单']
        })

    template1 = pd.DataFrame(template1_rows)
        

    template1_file_path = os.path.join(extract_dir, f'退货入库SMT凭证导入{last_month.strftime("%Y%m")}.xlsx')
    template1.to_excel(template1_file_path, index=False)

    # 输出模板2文件
    template2_rows = []
    inventory_adjustment = a_file_c[a_file_c['业务类型'] == '盘点调整']
    for index, row in inventory_adjustment.iterrows():
        if row['库存变动'] > 0:
            voucher_type = '入库'
            doc_type = '盘盈入库'
        else:
            voucher_type = '出库'
            doc_type = '盘亏出库'
        template2_rows.append({
            '凭证类型（必填）': voucher_type,
            '业务单据类型（条件必填）': doc_type,
            '账务日期（必填）': last_month.strftime("%Y-%m-%d"),
            '凭证子库（条件必填）': row['ERP仓库'],
            '产品编码（条件必填）': row['货品编码'],
            '数量（条件必填）': abs(row['库存变动']),
            '存货状态（必填）': 'BAD' if '在库残次' == row['库存类型'] else "NEW",
            '备注': row['业务主单']
        })

    scrap = a_file_c[a_file_c['业务类型'] == '报废']
    for index, row in scrap.iterrows():
        template2_rows.append({
            '凭证类型（必填）': '出库',
            '业务单据类型（条件必填）': '报废出库',
            '账务日期（必填）': last_month.strftime("%Y-%m-%d"),
            '凭证子库（条件必填）': row['ERP仓库'],
            '产品编码（条件必填）': row['货品编码'],
            '数量（条件必填）': abs(row['库存变动']),
            '存货状态（必填）': 'BAD' if '在库残次' == row['库存类型'] else "NEW",
            '备注': row['业务主单']
        })
    print(template2_rows)

    template2 = pd.DataFrame(template2_rows)
    if not template2.empty and '数量（条件必填）' in template2.columns and not template2[
        '数量（条件必填）'].isnull().all():
        template2 = template2.reindex(
            template2['数量（条件必填）'].abs().sort_values(ascending=False).index)
    else:
        print("template2 的 '数量（条件必填）' 列为空或不存在，跳过排序操作")

    template2_file_path = os.path.join(extract_dir, f'SMT库存盘点与报废凭证导入{last_month.strftime("%Y-%m")}.xlsx')
    template2.to_excel(template2_file_path, index=False)


if __name__ == '__main__':
    current_directory = os.getcwd() + "/测试数据"
    kucun_chengben_14_14_count_main(current_directory)
